package com.hongdee.scm.car.service.impl;

import com.hongdee.base.sendMsg.service.ISendMsgService;
import com.hongdee.common.dao.IHibernateDao;
import com.hongdee.common.dao.IImpHibernateDao;
import com.hongdee.common.dao.IImpJdbcDao;
import com.hongdee.common.dao.IJdbcDao;
import com.hongdee.common.util.TokenUtil;
import com.hongdee.scm.car.dao.ITScmCarDao;
import com.hongdee.scm.car.entity.TScmCar;
import com.hongdee.scm.car.entity.TScmCarPriceDetail;
import com.hongdee.scm.car.service.ITScmCarService;
import com.hongdee.scm.common.service.impl.Common;
import com.hongdee.scm.enterwarehouse.dao.ITScmEnterWareHouseDao;
import jdk.nashorn.internal.parser.Token;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * Created by Mr. Xu on 2017/5/4.
 */
@Service
public class TScmCarServiceImpl implements ITScmCarService {
    //    @Autowired
//    private ITBcOrganService organService;
    @Autowired
    private ITScmCarDao itScmCarDao;
    private static String tabName = "T_SCM_CAR";
    //车型对照表
    private String tabCarModel = " t_bc_carmodel";
    //配置对照表
    private String tabCarConfig = "t_bc_carconfigure";
    @Autowired
    private IImpJdbcDao impJdbcDao;
    @Autowired
    private IImpHibernateDao iImpHibernateDao;
    @Autowired
    private IJdbcDao iJdbcDao;
    @Autowired
    private IHibernateDao iHibernateDao;
    @Autowired
    private ITScmEnterWareHouseDao itScmEnterWareHouseDao;
    @Autowired
    private ISendMsgService iSendMsgService;

    /**
     * 保存修改
     *
     * @param data
     * @param uid
     * @return
     * @throws Exception
     */
    //判断价格为数字
    Pattern pattern = Pattern.compile("^[0-9]+(.[0-9]{0,4})?$");
    @Override
    public TScmCar saveOrUpdate(TScmCar data, String uid) throws Exception {

        //判断该车辆vin是否已在数据表里

        List list = this.itScmCarDao.list("select * from t_scm_car where vin = ? and company_id=?",data.getVin(),TokenUtil.getCompanyId(uid));
        if(list.size()>0){

            if("2".equals(((Map)list.get(0)).get("status"))){//销售出去后的回购的车
                data.setId(null);
            }else{//判断是否该车辆已存在
                throw new Exception("该车辆vin已存在");
            }
        }
        //id为空时则为添加
        if (data.getId() == null) {
            data.setCreateUser(uid);
            //默认在途
            data.setStatus("0");
            //默认可售
            data.setSaleType("1");
            //财务归属默认本店
            data.setFinancialBelong("1");
            //财务状态
            data.setFinanceType("0");
            //默认未销售
            //data.setSaleFlag("0");
            //默认未打印
           //data.setValidateStatus("0");
            //车辆默认非导入
            //data.setCarType("0");
            //默认未录入发票
           // data.setInvoiceStatus("0");
            //默认车辆出库状态为未锁定
           // data.setOutWarehouseStatus("0");
        } else {
           /* if (data.getCarType().equals("0")) {
                //id不为空时则为修改
                data.setUpdateUser(uid);
                data.setUpdateTime(Calendar.getInstance().getTimeInMillis());
            } else {
                throw new Exception("只能修改手动录入的车辆！");
            }*/
        }

        if(data.getEstimatePrice()!=null&&!data.getEstimatePrice().equals("")){
            Matcher isNum = pattern.matcher(data.getEstimatePrice());
            if( !isNum.matches() ){
                throw new Exception("暂估价格应输入数字!");
            }
        }
        data.setCompanyId(TokenUtil.getCompanyId(uid));
        //刷新车系
        Map series = this.itScmCarDao.info("select * from t_bc_car_series where id = ? and res_status = '1' ", data.getCarSeriesId());
        if (!series.isEmpty()) {
            data.setCarSeriesCode(series.get("carSeriesCode").toString());
            data.setCarSeries(series.get("carSeriesName").toString());
        }
        //刷新车型
        Map model = this.itScmCarDao.info("select * from t_bc_carmodel where id = ? and res_status = '1' ",data.getCarModelId());
        if(!model.isEmpty()){
            data.setCarModelCode(model.get("modelCode").toString());
            data.setCarModel(model.get("modelName").toString());
        }
        //刷新配置
        Map configure = this.itScmCarDao.info("select * from t_bc_carconfigure where id = ? and res_status = '1' ", data.getConfigureId());
        if (!configure.isEmpty()) {
            data.setConfigure(configure.get("configureName").toString());
            data.setConfigureCode(configure.get("configureCode").toString());
        }
        //刷新外观
        Map appearance = this.itScmCarDao.info("select appearance_name from t_scm_appearance where car_series_id" +
                    "=? and  appearance_code=? ", series.get("id"), data.getAppearanceCode());
        if(!appearance.isEmpty()){
            data.setAppearanceName(appearance.get("appearanceName").toString());
        }
        //刷新内饰
        Map trim = this.itScmCarDao.info("select * from t_scm_interior where car_series_id =? and interior_code=?",series.get("id"),data.getTrimCode());
        if(!trim.isEmpty()){
            data.setTrimName(trim.get("interiorName").toString());
        }
        this.itScmCarDao.saveOrUpdate(data);
        return data;
    }

    /**
     * 整车信息分页 ———采购在途
     *
     * @param data
     * @param start
     * @param limit
     * @param uid
     * @return
     * @throws Exception
     */
    @Override
    public Map page(Map data, String start, String limit, String uid) throws Exception {

        StringBuffer sql = new StringBuffer();
        List params = new ArrayList();
        sql.append("select p.*,tbm.model_name,tcc.configure_name,w.warehouse_name,DATE_FORMAT(p.purchase_date,'%Y-%m-%d') purchase_date1" +
                " from " + tabName + " p left join " + tabCarModel + " tbm on p.car_model_id=tbm.id")
                .append(" left join " + tabCarConfig + " tcc on p.configure_id=tcc.id")
                .append(" left join t_scm_sale_order_detail o on p.id = o.car_id ")
                .append(" left join t_scm_warehouse w on w.id = o.warehouse")
                .append(" where p.company_id = ? ");
        params.add(TokenUtil.getCompanyId(uid));
        //车辆状态
        if (data.get("status") != null && !data.get("status").toString().equals("")) {
                sql.append(" and p.status = ? ");
                params.add(data.get("status"));
        }
        //vin查询
        if(data.get("vin")!=null&&!data.get("vin").toString().equals("")){
            sql.append(" and p.vin like ? ");
            params.add("%"+data.get("vin")+"%");
        }
        //判断采购单位
//        if (data.get("carModel") != null && !data.get("carModel").toString().equals("")) {
//            sql.append(" and p.car_model like ? ");
//            params.add("%" + data.get("carModel") + "%");
//        }
         //车辆销售状态
        if (data.get("saleType") != null && !data.get("saleType").toString().equals("")) {
            sql.append(" and p.sale_type = ? ");
            params.add(data.get("saleType"));
        }
        //采购日期起
        if (data.get("enterWarehouseDateStart") != null && !data.get("enterWarehouseDateStart").toString().equals("")) {
            sql.append(" and p.purchase_date >= ? ");
            params.add(data.get("enterWarehouseDateStart"));
        }
        //采购日期止
        if (data.get("enterWarehouseDateStop") != null && !data.get("enterWarehouseDateStop").toString().equals("")) {
            sql.append(" and p.purchase_date <= ? ");
            params.add(data.get("enterWarehouseDateStop"));
        }
        //车系
        if (data.get("carSeriesCode") != null && !data.get("carSeriesCode").equals("")) {
            sql.append(" and p.car_series_id = ? ");
            params.add(data.get("carSeriesCode"));
            //车型代码
            if (data.get("carModelCode") != null && !data.get("carModelCode").equals("")) {
                sql.append(" and p.car_model_id = ? ");
                params.add(data.get("carModelCode"));
                //车辆配置
                if (data.get("carConfigureCode") != null && !data.get("carConfigureCode").equals("")) {
                    sql.append(" and p.configure_id = ?");
                    params.add(data.get("carConfigureCode"));

                }
            }
        }
       //车型代码
        if (data.get("carModelCode") != null && !data.get("carModelCode").equals("")) {
            sql.append(" and p.car_model_id = ? ");
            params.add(data.get("carModelCode"));
        }
       /* //初次入库日期起
        if(data.get("enterDateStart")!=null && !data.get("enterDateStart").equals("")){
            sql.append(" and DATE_FORMAT(p.enter_warehouse_date_start,'%Y-%m-%d') >= ?");
            params.add(data.get("enterDateStart"));
        }
        //初次入库日期到
        if(data.get("enterDateStop")!=null && !data.get("enterDateStop").equals("")){
            sql.append(" and DATE_FORMAT(p.enter_warehouse_date_start,'%Y-%m-%d') <= ?");
            params.add(data.get("enterDateStop"));
        }*/
        //销售日期起
        if(data.get("saleDateStart")!=null && !data.get("saleDateStart").equals("")){
            sql.append(" and DATE_FORMAT(p.sale_date,'%Y-%m-%d') >= ?");
            params.add(data.get("saleDateStart"));
        }
        //销售日期到
        if(data.get("saleDateStop")!=null && !data.get("saleDateStop").equals("")){
            sql.append(" and DATE_FORMAT(p.sale_date,'%Y-%m-%d') <= ?");
            params.add(data.get("saleDateStop"));
        }
        sql.append(" order by p.create_time desc");
        return this.itScmCarDao.page(sql.toString(), params.toArray(), start, limit);
    }

    /**
     * 整车信息详细
     *
     * @param id
     * @param uid
     * @return
     * @throws Exception
     */
    @Override
    public Map info(String id, String uid) throws Exception {
        List params = new ArrayList();
        String sql = "select * from " + tabName + " p where id = ? and p.company_id= ?";
        params.add(id);
        params.add(TokenUtil.getCompanyId(uid));
        return this.itScmCarDao.info(sql, params.toArray());
    }

    /**
     * 车辆列表查询
     *
     * @param uid
     * @param data
     * @return
     * @throws Exception
     */
    @Override
    public List list(String uid, Map data) throws Exception {
        List params = new ArrayList();
        Common com = new Common();
        Boolean hasWhere = false;
        StringBuffer sqlbuf = new StringBuffer();
        sqlbuf.append("select p.*, ss.warehouse_id, ss.enter_date enter_warehouse_date from " + tabName +" p left join t_scm_stock ss on p.id = ss.car_id ");

        //默认只能看自己组织下数据
        String cid = TokenUtil.getCompanyId(uid);
        if(!cid.isEmpty()){
            hasWhere = com.appendWhereIfNeed(sqlbuf, hasWhere);
            sqlbuf.append(" p.company_id = ? ");
            params.add(cid);
        }

        //调拨只查询可调拨的车辆
        if(data.get("sInvoice") != null && !data.get("sInvoice").equals("")) {
            if (data.get("sInvoice").equals("1")) {//调拨
                hasWhere = com.appendWhereIfNeed(sqlbuf, hasWhere);
                sqlbuf.append("ss.allocation_status='0' ");
                //财务状态
                if(data.get("financeType") != null && !data.get("financeType").equals("")){
                    hasWhere = com.appendWhereIfNeed(sqlbuf, hasWhere);
                    sqlbuf.append(" ( p.finance_type = '2' or  p.finance_type ='3') ");
                }
                //车辆状态
                if (data.get("status") != null && !data.get("status").toString().equals("")) {
                    hasWhere = com.appendWhereIfNeed(sqlbuf, hasWhere);
                    sqlbuf.append(" p.status   = ? and p.sale_type = '1' ");
                    params.add(data.get("status"));
                }
            }else{ //发票登记
                //红冲车辆查询
                if (data.get("isRed") != null && !StringUtils.isEmpty(data.get("isRed"))) {
                    if(data.get("isRed").equals("4")){ //整红冲车辆
                        hasWhere = com.appendWhereIfNeed(sqlbuf, hasWhere   );
                        sqlbuf.append(" p.finance_type = '4' ");
                    }
                    if(data.get("isRed").equals("2")){ //暂估
                        hasWhere = com.appendWhereIfNeed(sqlbuf, hasWhere   );
                        sqlbuf.append(" p.finance_type = '2' and p.status <> '0' ");
                    }
                    if(data.get("isRed").equals("1")){ //未暂估
                        hasWhere = com.appendWhereIfNeed(sqlbuf, hasWhere   );
                        sqlbuf.append(" p.finance_type = '1' ");

                        //车辆状态
                        if (data.get("status") != null && !data.get("status").toString().equals("")) {
                            hasWhere = com.appendWhereIfNeed(sqlbuf, hasWhere);
                            sqlbuf.append(" p.status   = ? ");
                            params.add(data.get("status"));
                        }
                    }
                }
            }
        }

        //vin条件模糊
        if (data.get("vin") != null && !data.get("vin").toString().equals("")) {
            hasWhere = com.appendWhereIfNeed(sqlbuf, hasWhere);
            sqlbuf.append("p.vin like ? ");
            params.add("%" + data.get("vin") + "%");
        }
        //入库单号模糊
       /* if (data.get("enterWarehouseNum") != null && !data.get("enterWarehouseNum").toString().equals("")) {
            hasWhere = com.appendWhereIfNeed(sqlbuf, hasWhere);
            sqlbuf.append(" enter_warehouse_num like ? ");
            params.add("%" + data.get("enterWarehouseNum") + "%");
        }*/
        //提单号模糊
        if (data.get("oldNum") != null && !data.get("oldNum").toString().equals("")) {
            hasWhere = com.appendWhereIfNeed(sqlbuf, hasWhere);
            sqlbuf.append(" p.old_num like ? ");
            params.add("%" + data.get("oldNum") + "%");
        }
        //品牌
        if (data.get("brand") != null && !data.get("brand").toString().equals("")) {
            hasWhere = com.appendWhereIfNeed(sqlbuf, hasWhere);
            sqlbuf.append(" p.brand = ? ");
            params.add(data.get("brand"));
        }
        //车系
        if (data.get("carSeriesCode") != null && !data.get("carSeriesCode").equals("")) {
            hasWhere = com.appendWhereIfNeed(sqlbuf, hasWhere);
            sqlbuf.append(" p.car_series_id = ? ");
            params.add(data.get("carSeriesCode"));
            }
        //车型代码
         if (data.get("carModelCode") != null && !data.get("carModelCode").equals("")) {
           hasWhere = com.appendWhereIfNeed(sqlbuf, hasWhere);
           sqlbuf.append(" p.car_model_id = ? ");
           params.add(data.get("carModelCode"));
           }

        //车辆配置
        if (data.get("carConfigureCode") != null && !data.get("carConfigureCode").equals("")) {
            hasWhere = com.appendWhereIfNeed(sqlbuf, hasWhere);
            sqlbuf.append(" p.configure_id = ?");
            params.add(data.get("carConfigureCode"));

        }

        //仓库筛选
        if (data.get("warehouse") != null && !StringUtils.isEmpty(data.get("warehouse"))) {
            hasWhere = com.appendWhereIfNeed(sqlbuf, hasWhere);
            sqlbuf.append(" ss.warehouse_id = ? ");
            params.add(data.get("warehouse"));
        }
//        if(data.get("allocationFlag")!=null&&!StringUtils.isEmpty(data.get("allocationFlag"))&&data.get("allocationFlag").equals("1")){
//            hasWhere = com.appendWhereIfNeed(sqlbuf, hasWhere);
//            sqlbuf.append(" vin not in (select vin from t_scm_allocation_order_detail where allocation_order_id where in " +
//                    " select id from t_scm_allocation_order )  ");
//            params.add(data.get("warehouse"));
//        }
        //hasWhere = com.appendWhereIfNeed(sqlbuf, hasWhere);
        //sqlbuf.append(" ( invoice_status = '0' or (invoice_status='1' and is_red='1')) ");
        // sqlbuf.append(" vin not in (select vin from T_Scm_Input_Invoice_detail) ");

        sqlbuf.append(" order by purchase_date desc");
        return this.itScmCarDao.list(sqlbuf.toString(), params.toArray());
    }


    /**
     * 待入库车辆列表查询
     *
     * @param uid
     * @param data
     * @return
     * @throws Exception
     */
    @Override
    public List listCar(String uid, Map data) throws Exception {
        List params = new ArrayList();
        Common com = new Common();
        Boolean hasWhere = false;
        StringBuffer sqlbuf = new StringBuffer();
        sqlbuf.append("select cc.configure_name,cm.model_name,c.*  from " + tabName + " c  left join t_bc_carConfigure cc on c.configure_id = cc.id left join t_bc_carModel cm on cm.id = c.car_model_id ");
        String cid = TokenUtil.getCompanyId(uid);
        if(cid!=null&&cid.equals("")){
            hasWhere = com.appendWhereIfNeed(sqlbuf, hasWhere);
            sqlbuf.append(" c.company_id= ? ");
            params.add(cid);
        }
        //vin条件模糊
        if (data.get("vin") != null && !data.get("vin").toString().equals("")) {
            hasWhere = com.appendWhereIfNeed(sqlbuf, hasWhere);
            sqlbuf.append(" c.vin like ? ");
            params.add("%" + data.get("vin") + "%");
        }
        //入库单号模糊
        if (data.get("enterWarehouseNum") != null && !data.get("enterWarehouseNum").toString().equals("")) {
            hasWhere = com.appendWhereIfNeed(sqlbuf, hasWhere);
            sqlbuf.append(" c.enter_warehouse_num like ? ");
            params.add("%" + data.get("enterWarehouseNum") + "%");
        }
        //提单号模糊
        if (data.get("oldNum") != null && !data.get("oldNum").toString().equals("")) {
            hasWhere = com.appendWhereIfNeed(sqlbuf, hasWhere);
            sqlbuf.append(" c.old_num like ? ");
            params.add("%" + data.get("oldNum") + "%");
        }
        //品牌
        if (data.get("brand") != null && !data.get("brand").toString().equals("")) {
            hasWhere = com.appendWhereIfNeed(sqlbuf, hasWhere);
            sqlbuf.append(" c.brand = ? ");
            params.add(data.get("brand"));
        }
        //车型
        if (data.get("carModel") != null && !data.get("carModel").toString().equals("")) {
            hasWhere = com.appendWhereIfNeed(sqlbuf, hasWhere);
            sqlbuf.append(" c.car_model = ? ");
            params.add(data.get("carModel"));
        }
        //是否录入发票
        if (data.get("invoiceStatus") != null && !data.get("invoiceStatus").equals("")) {
            hasWhere = com.appendWhereIfNeed(sqlbuf, hasWhere);
            sqlbuf.append(" c.finance_type = ? ");
            params.add(data.get("invoiceStatus"));
        }
        //仓库
        if (data.get("warehouse") != null && !data.get("warehouse").equals("")) {
            hasWhere = com.appendWhereIfNeed(sqlbuf, hasWhere);
            sqlbuf.append(" p.warehouse = ? ");
            params.add(data.get("warehouse"));
        }
        hasWhere = com.appendWhereIfNeed(sqlbuf, hasWhere);
        sqlbuf.append(" c.vin not in (select vin from T_Scm_Stock) ");
        sqlbuf.append(" order by c.enter_warehouse_date");
        return this.itScmCarDao.list(sqlbuf.toString(), params.toArray());
    }


    /**
     * 整车信息导入第二数据源分页
     *
     * @param start
     * @param limit
     * @param oid
     * @return
     * @throws Exception
     */
    @Override
    public Map impPage(String start, String limit, String oid) throws Exception {
        StringBuffer sqlBuffer = new StringBuffer();
        sqlBuffer.append("select * from " + tabName);
        List<String> paramList = new ArrayList<String>();
        Common common = new Common();
        Boolean hasWhere = false;
        if (!StringUtils.isEmpty(oid)) {
            hasWhere = common.appendWhereIfNeed(sqlBuffer, hasWhere);
            sqlBuffer.append(" company_id = ? ");
            paramList.add(oid);
        }
        sqlBuffer.append(" order by create_time desc");
        return this.impJdbcDao.queryForPageMap(start, limit, sqlBuffer.toString(), paramList.toArray());
    }

    /**
     * @param data 核销数据的实体
     * @return Map
     * @throws Exception
     * @desc 整车信息批量导入确认
     * @author 徐钦勇
     */
    @Override
    public List impConfirms(List<Map<Object, String>> data, String uid) throws Exception {

        // String sql1 = "select * from " + tabName + " where vin = ? and company_id = ? limit 1";
        List<Object> idList = new ArrayList();
        String tempId;
        Map existData;
        TScmCar scmCar;
        List list = this.nonImportCars(data,uid);
        List<TScmCar> carList = new ArrayList<>();
        List<TScmCarPriceDetail> carPriceDetailList = new ArrayList<>();

        for (Map car : data) {
            //查询源数据实体
            tempId = car.get("id").toString();
            scmCar = (TScmCar) this.iImpHibernateDao.findEntity(TScmCar.class, tempId);
            //源数据主键
            idList.add(tempId);
            TScmCar c = confirmTempCarInfo(uid, scmCar);
            carList.add(c);

            //插入价格信息表
            TScmCarPriceDetail carPrice = new TScmCarPriceDetail();
            carPrice.setCarId(c.getId());
            carPrice.setVin(c.getVin());
            carPrice.setCreateUser(uid);
            carPrice.setCreateTime(Calendar.getInstance().getTimeInMillis());
            carPrice.setStatus(0);
            carPrice.setOutType(0);
            carPriceDetailList.add(carPrice);
        }
        //保存车辆
        this.itScmCarDao.saveOrUpdateList(carList);
        //保存导入表
        this.itScmCarDao.saveOrUpdateList(carPriceDetailList);
        //刷新车系车型配置
        this.initializeCarInfo(uid);
        //满1000时，删除数据
        this.impJdbcDao.batchDel(tabName, "id", idList);
        idList.clear();
        //当没有车型车系的时候发短信告诉管理员
        sendMessage(uid);
        return list;
    }

    /**
     * 导入时加入提示（该车已在库或已销售）--批量确认
     * @param uid
     * @return
     */
    private List nonImportCars(List<Map<Object, String>> data,String uid) throws Exception{

        List<Map<String,Object>> ll = new ArrayList<>();
        String cid = TokenUtil.getCompanyId(uid);
        //在库车
        StringBuffer sql = new StringBuffer();
        List params = new ArrayList();
        sql.append("select * from t_scm_stock where company_id=? and vin in (");
        params.add(cid);
        for (Map<Object, String> map : data) {
            sql.append(" ? , ");
            params.add(map.get("vin"));
        }
        sql.append(" '' ) ");
        List<Map<String,Object>> list = this.itScmCarDao.list(sql.toString(),params.toArray());
        if(list.size()>0){
            for(Map<String,Object> map:list){
              Map m = new HashMap();
              m.put("type","在库");
              m.put("vin",map.get("vin"));
              ll.add(m);
            }
        }
        //销售车
        StringBuffer sqlSale = new StringBuffer();
        List param = new ArrayList();
        sqlSale.append("select * from t_scm_sale_order_detail where company_id=? and vin in (");
        param.add(cid);
        for (Map<Object, String> map : data) {
            sqlSale.append(" ? , ");
            param.add(map.get("vin"));
        }
        sqlSale.append(" '' ) ");
        List<Map<String,Object>> list1 = this.itScmCarDao.list(sqlSale.toString(),param.toArray());
        if(list1.size()>0){
            for(Map<String,Object> map:list1){
                Map m = new HashMap();
                m.put("type","已售");
                m.put("vin",map.get("vin"));
                ll.add(m);
            }
        }
        return ll;
    }

    //刷新暂估价格
    @Override
    public void refreshEstimatePrice(String uid) throws Exception {
        //所有暂估价为空、为0的车辆,增加配置ID关联,按单位刷新，待完成zhc 20170913
        String cid = TokenUtil.getCompanyId(uid);
        this.itScmCarDao.executeSQL("UPDATE t_scm_car c INNER JOIN t_scm_estimatedprice e ON c.configure_id = e.configure_code " +
                        "SET c.estimate_price = e.price WHERE c.company_id = ? AND ( c.estimate_price IS NULL OR c.estimate_price = '' " +
                        "or c.estimate_price = '0' ) AND c.configure_id IS NOT NULL ",cid);
    }

    /**
     * @return
     * @throws Exception
     * @desc 整车信息全部导入确认
     * @author 徐钦勇
     */
    @Override
    public List impConfirmAll(String uid) throws Exception {
        //获取公司 ID
        String companyId = TokenUtil.getCompanyId(uid);
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        //编写查询源数据 HQL
        String hql = "from TScmCar where companyId = :companyId";
        //设置参数
        Map<String, Object> params = new HashMap();
        params.put("companyId", companyId);
        //查询公司下的全部车辆
        List<TScmCar> tScmCar = iImpHibernateDao.findEntityListByHqlWithParams(hql, params);
        if (tScmCar == null) {
            throw new Exception("无效的数据！");
        }
        List list = this.nonImportCarsAll(tScmCar,uid);
        //根据vin判断目标数据是否已存在
        List<Object> idList = new ArrayList();
        String tempId;
        Map existData;
        List<TScmCar> carList = new ArrayList<>();
        List<TScmCarPriceDetail> carPriceDetailList = new ArrayList<>();
        for (TScmCar car : tScmCar) {
            //源数据主键
            tempId = car.getId();
            //源数据主键
            idList.add(tempId);

            TScmCar c = confirmTempCarInfo(uid, car);
            if(c==null){
                continue;
            }
            carList.add(c);

            //插入价格信息表
            TScmCarPriceDetail carPrice = new TScmCarPriceDetail();
            carPrice.setCarId(c.getId());
            carPrice.setVin(c.getVin());
            carPrice.setCreateUser(uid);
            carPrice.setCreateTime(Calendar.getInstance().getTimeInMillis());
            carPrice.setStatus(0);
            carPrice.setOutType(0);
            carPriceDetailList.add(carPrice);

            //满1000时，删除数据
            if (idList.size() == 1000) {
                this.impJdbcDao.batchDel(tabName, "id", idList);
                idList.clear();
            }
        }
        //保存车辆
        this.itScmCarDao.saveOrUpdateList(carList);
        //保存导入表
        this.itScmCarDao.saveOrUpdateList(carPriceDetailList);
        //刷新
        this.initializeCarInfo(uid);
        //删除遗留数据
        if (idList.size() > 0) {
            this.impJdbcDao.batchDel(tabName, "id", idList);
            idList.clear();
        }
        //没有匹配上车型车系时发送短信给管理员
        sendMessage(uid);

        return list;
    }

    /**
     * 导入时加入提示（该车已在库或已销售）--全部确认
     * @param uid
     * @return
     */
    private List nonImportCarsAll(List<TScmCar> data,String uid) throws Exception{

        List<Map<String,Object>> ll = new ArrayList<>();
        String cid = TokenUtil.getCompanyId(uid);
        //在库车
        StringBuffer sql = new StringBuffer();
        List params = new ArrayList();
        sql.append("select * from t_scm_stock where company_id=? and vin in (");
        params.add(cid);
        for (TScmCar map : data) {
            sql.append(" ? , ");
            params.add(map.getVin());
        }
        sql.append(" '' ) ");
        List<Map<String,Object>> list = this.itScmCarDao.list(sql.toString(),params.toArray());
        if(list.size()>0){
            for(Map<String,Object> map:list){
                Map m = new HashMap();
                m.put("type","在库");
                m.put("vin",map.get("vin"));
                ll.add(m);
            }
        }
        //销售车
        StringBuffer sqlSale = new StringBuffer();
        List param = new ArrayList();
        sqlSale.append("select * from t_scm_sale_order_detail where company_id=? and vin in (");
        param.add(cid);
        for (TScmCar map : data) {
            sqlSale.append(" ? , ");
            param.add(map.getVin());
        }
        sqlSale.append(" '' ) ");
        List<Map<String,Object>> list1 = this.itScmCarDao.list(sqlSale.toString(),param.toArray());
        if(list1.size()>0){
            for(Map<String,Object> map:list1){
                Map m = new HashMap();
                m.put("type","已售");
                m.put("vin",map.get("vin"));
                ll.add(m);
            }
        }
        return ll;
    }

    /**
     * 发送短信
     * @param uid
     */
    private void sendMessage(String uid) throws Exception{

        List<Map<String,Object>> dataList = new ArrayList<>();
        List<Map<String,Object>> list  = this.itScmCarDao.list("select c.*,u.u_name from t_scm_car c inner join t_bc_user u on u.id = c.create_user " +
                " where (c.car_model_id is null or c.car_model_id = '') and c.company_id=? and c.status = '0'",TokenUtil.getCompanyId(uid));
        StringBuffer content = new StringBuffer();
        Map map = new HashMap();
        if(list.size()>0){
            for(int i=0;i<list.size();i++) {
                Map mm = list.get(i);
                if(i==0){
                    content.append(mm.get("uName") + "导入的车辆vin=" + mm.get("vin") );
                }
                if(i!=0&&i!=list.size()-1){
                    content.append(","+mm.get("vin"));
                }
                if(i==list.size()-1){
                    content.append(",未维护车系,车型,配置信息");
                 /*   map.put("content", content);
                    map.put("mobile", "15562606235");//王绍辉电话*/
                }
            }
            dataList.add(map);
            List<String> mobile = new ArrayList<>();
            mobile.add("15562606235");//王绍辉电话
            mobile.add("13626418067"); //丁华龙
            mobile.add("15564491072");//信玉凯
            mobile.add("17854125895"); //王文辉
            //iSendMsgService.sendPost(dataList,"10661234");
            iSendMsgService.sendGet(content.toString(),mobile,"10661234");
        }
    }

    private static final String sqlCheckCarExist = "select id, vin from " + tabName + " where vin = ? and company_id = ? limit 1";
    private static  final DecimalFormat decimalFormat = new DecimalFormat("#.00");
    private static final SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    private TScmCar confirmTempCarInfo(String uid, TScmCar scmCar)
    {
        try {
            if (StringUtils.isEmpty(scmCar.getVin())){
                return null;
            }
            //根据公司与vin判断车辆是否存在
            Map existData = this.iJdbcDao.queryForMapBySqlWithParams(sqlCheckCarExist, scmCar.getVin(), scmCar.getCompanyId());

            //判断数据是否已存在
            if (existData.size() == 0) {
                //将日期改为'YYMMDD'
                //采购日期转化
                if ( null != scmCar.getPurchaseDate() && Pattern.matches("^[0-9]*$", scmCar.getPurchaseDate())) {
                    scmCar.setPurchaseDate(sdf.format(new Date(Long.valueOf(scmCar.getPurchaseDate()))));
                }

                //配货日期转化
                if ( null != scmCar.getDistributionDate() && Pattern.matches("^[0-9]*$", scmCar.getDistributionDate())) {
                    scmCar.setDistributionDate(sdf.format(new Date(Long.valueOf(scmCar.getDistributionDate()))));
                }

                //发货日期转化
                if ( null != scmCar.getSendDate() && Pattern.matches("^[0-9]*$", scmCar.getSendDate())) {
                    scmCar.setSendDate(sdf.format(new Date(Long.valueOf(scmCar.getSendDate()))));
                }

                //品牌
                scmCar.setBrand(TokenUtil.getCompany(uid).get("oCarBrand"));
                ////
                //默认在途
                scmCar.setStatus("0");
                //财务归属默认本店
                scmCar.setFinancialBelong("1");
                scmCar.setId(null);
                scmCar.setCreateTime(System.currentTimeMillis());
                scmCar.setUpdateTime(System.currentTimeMillis());
                //财务状态
                scmCar.setFinanceType("0");
                //默认不可销售
                scmCar.setSaleType("2");

                ///////暂估价格除以1.17
                if ( !StringUtils.isEmpty(scmCar.getEstimatePrice() ))
                {
                    try {  ///防止转换导致保存失败
                        scmCar.setEstimatePrice(scmCar.getEstimatePrice().replaceAll(",",""));
                        scmCar.setPurchasePrice(scmCar.getEstimatePrice());
                        Double ep = Double.valueOf(scmCar.getEstimatePrice());
                        scmCar.setEstimatePrice(decimalFormat.format(ep/1.17));
                    }
                    catch (Exception e)
                    {

                    }
                }
                //保存或更新车辆信息
                //this.iHibernateDao.saveOrUpdateEntity(scmCar);
                //2017-05-24新增车辆价格单表

                return scmCar;
            }
        }
        catch (Exception e)
        {
            return null;
        }
        return null;
    }


    /*
    * 导入确认后统一匹配车辆的基本信息：车系、车型、配置、外观、内饰、暂估价格、供货单位
     */
    private void initializeCarInfo(String uid)
    {

        //刷车型ID&配置ID&车系ID  ////统一刷新数据，不再单独刷入了
        try
        {
            this.refreshCarSMC(uid);

            ////外观\内饰颜色
            this.refershCarAppearanceAndInterior(uid);

            ///刷新暂估价格
            refreshEstimatePrice(uid);

            //购货单位,没有根据公司ID进行过滤处理
            this.itScmCarDao.executeSQL("update t_scm_car c inner join t_scm_providecompany p on p.company_id = c.company_id" +
                    " set c.provide_company_name = p.customer_name where p.vin like CONCAT('%',SUBSTR(c.vin,1,1),'%')" +
                    " and p.status = '1' and p.customer_type ='0' and (p.customer_classify = '0' or p.customer_classify = '1') and p.company_id=?",TokenUtil.getCompanyId(uid) );
        }
        catch (Exception e)
        {}

    }

    /**
     * 整车导入批量删除
     *
     * @param data
     * @param uid
     * @throws Exception
     */
    @Override
    public void impDels(List<Map<Object, String>> data, String uid) throws Exception {

        List<Object> idList = new ArrayList();
        for (Map map : data) {
            idList.add(map.get("id"));
        }
        this.impJdbcDao.batchDel(tabName, "id", idList);
    }


    /**
     * @return
     * @throws Exception
     * @desc 删除整车信息全部导入数据
     * @author 徐钦勇
     */
    @Override
    public void impDelAll(String uid) throws Exception {

        Map map = new HashMap();
        String companyId = TokenUtil.getCompanyId(uid);
        String hql = " delete from TScmCar where companyId =:companyId";
        map.put("companyId", companyId);
        //System.out.println(companyId);

        this.iImpHibernateDao.executeHqlWithParams(hql, map);
    }

    /**
     * 暂估价格维护
     *
     * @param id
     * @param price
     * @param uid
     * @throws Exception
     */
    @Override
    public void updateEstimatePprice(String id, String price, String uid) throws Exception {
        StringBuffer sql = new StringBuffer();
        sql.append(" update T_Scm_Car set estimate_price=? where id=? ");
        List params = new ArrayList();
        params.add(price);
        params.add(id);
        this.itScmCarDao.executeSQL(sql.toString(), params.toArray());
        //更新T_Scm_Car_Price_Detail表车辆暂估
        this.itScmCarDao.executeSQL("update T_Scm_Car_Price_Detail set estimate_price = ? " +
                " where vin in (select vin from T_Scm_Car where id = ? )",params.toArray());
    }

    /**
     * 根据vin前八位刷新车型ID和配置ID
     *
     * @param vin
     * @return
     * @throws Exception
     */
    private Map flashModelAndConfig(String vin, String modelCode, String configCode) throws Exception {
        //根据vin前八位查询车型ID,配置ID
        Map CarModelIdMap = flashModelId(vin, modelCode);
        Map CarConfigIdMap = flashConfigId(vin, configCode);
        //拼输出数据
        Map data = new HashMap();
        data.put("modelId", CarModelIdMap.get("id"));
        data.put("carSeriesId", CarModelIdMap.get("seriesId"));
//        data.put("modelName",CarModelIdMap.get("modelName"));
//        data.put("modelCode",CarModelIdMap.get("modelCode"));
        data.put("configId", CarConfigIdMap.get("id"));
//        data.put("configName",CarConfigIdMap.get("configureName"));
//        data.put("configCode",CarConfigIdMap.get("configureCode"));
        return data;
    }

    /**
     * 根据vin适配码刷新车型ID
     *
     * @param vin
     * @return
     * @throws Exception
     */
    //根据vin前八位查询车型ID
    private Map flashModelId(String vin, String modelCode) throws Exception {
        //根据vin前八位查询车型ID
        //String vin8 = vin.substring(0, 8);
        Map mapOfVin = this.itScmCarDao.info("select * from " + tabCarModel + " where vin_code !='' and INSTR( ? ,vin_code)=1 ", vin);
        Map mapOfModelCode = this.itScmCarDao.info("select * from " + tabCarModel + " where model_code =? ", modelCode);
        if (mapOfVin.size() == 0) {
            return mapOfModelCode;
        } else {return mapOfVin;}
    }

    /**
     * 根据vin适配码刷新配置ID
     *
     * @param vin
     * @return
     * @throws Exception
     */
    private Map flashConfigId(String vin, String configCode) throws Exception {
        //根据vin前八位查询配置ID
        Map mapOfVin = this.itScmCarDao.info("select * from " + tabCarConfig + " where vin_code !='' and INSTR( ? ,vin_code)=1  ", vin);
        Map mapOfConfigCode = this.itScmCarDao.info("select * from " + tabCarConfig + " where configure_code =? ", configCode);
        if (mapOfVin.size() == 0) {
            return mapOfConfigCode;
        } else {return mapOfVin;}
    }

    /**
     * 暂时刷新没有车型ID车辆的车型ID
     *
     * @param uid
     * @throws Exception
     */
    @Override
    public void flashAllCarModelId(String uid) throws Exception {
        this.itScmCarDao.executeSQL("UPDATE t_scm_car c INNER JOIN t_bc_carmodel m ON m.brand_code = c.brand" +
                "  AND ( INSTR(c.vin, m.vin_code) = 1 OR c.car_model_code = m.model_code" +
                " ) SET c.car_model_id = m.id, c.car_series_id = m.series_id, " +
                "  c.car_model = ( CASE WHEN c.car_model IS NULL THEN m.model_name ELSE c.car_model END )" +
                " WHERE c.company_id =? AND (c.car_model_id IS NULL  or c.car_model_id ='') ", TokenUtil.getCompanyId(uid));

        ////更新车系名称，从上面分开处理，车系代码 不再进行填写
        this.itScmCarDao.executeSQL("UPDATE t_scm_car c INNER join t_bc_car_series s ON c.car_series_id = s.id " +
                "  SET c.car_series = s.car_series_name " +
                " WHERE c.company_id =? AND (  c.car_series is null or c.car_series ='') ", TokenUtil.getCompanyId(uid));
    }

    /**
     * 暂时刷新没有配置ID车辆的配置ID
     *
     * @param uid
     * @throws Exception
     */
    @Override
    public void flashAllCarConfigId(String uid) throws Exception {
        ////正规按匹配完车型后进行配置的匹配
        this.itScmCarDao.executeSQL("update t_scm_car c INNER JOIN t_bc_carconfigure m on c.car_model_id=m.model_id and (INSTR(c.vin,m.vin_code)=1 or (c.configure_code = m.configure_code) or ( c.configure_code  REGEXP m.configure_regular_code ) = 1 ) " +
                "set c.configure_id=m.id, c.configure=(case when c.configure is null then m.configure_name else c.configure end) " +
                "where c.company_id=? and c.configure_id is null", TokenUtil.getCompanyId(uid));
        ////由配置向车型的匹配，第一步
        this.itScmCarDao.executeSQL("update t_scm_car c INNER JOIN t_bc_carconfigure m on  m.brand_code=c.brand and (INSTR(c.vin,m.vin_code)=1 or (c.configure_code = m.configure_code) or ( c.configure_code  REGEXP m.configure_regular_code ) = 1 ) " +
                "set c.configure_id=m.id, c.car_model_id=m.model_id, c.configure=(case when c.configure is null then m.configure_name else c.configure end) " +
                "where c.company_id=? and (c.configure_id is null or c.car_model_id is null) ", TokenUtil.getCompanyId(uid));
        ////由配置向车型的匹配，第二步
        this.itScmCarDao.executeSQL("update t_scm_car c INNER JOIN t_bc_carmodel m on m.id=c.car_model_id  " +
                "set  c.car_series_id=m.series_id,c.car_model=(case when c.car_model is null then m.model_name else c.car_model end) " +
                "where c.company_id=? and car_series_id is null and car_model_id is not null", TokenUtil.getCompanyId(uid));
    }

    /**
     * 批量删除导入数据错误车辆信息
     *
     * @param ids
     * @param uid
     * @throws Exception
     */
    @Override
    public void deleteUserlessCar(String[] ids, String uid) throws Exception {
        StringBuffer delSql = new StringBuffer();
        List params = new ArrayList();
        //管理员可删除
        if (!TokenUtil.isCompanyAdmin(uid)) {
            throw new Exception("非管理员不可删除车辆！");
        }
        delSql.append("delete from " + tabName + " where vin in ( ");
        for (Object vin : ids) {
            //判断此vin是否存在采购发票
            if (this.itScmCarDao.list("select * from T_Scm_Input_Invoice_detail where vin = ? ", vin).size() == 0) {
                delSql.append(" ?, ");
                params.add(vin);
            }
        }
        delSql.append(" '')");
        this.itScmCarDao.executeSQL(delSql.toString(), params.toArray());
    }

    /**
     * 车辆分页
     *
     * @param data
     * @param start
     * @param limit
     * @param status
     * @param uid
     * @return
     * @throws Exception
     */
    @Override
    public Map page1(Map data, String status, String start, String limit, String uid) throws Exception {

        StringBuffer sql = new StringBuffer();
        List params = new ArrayList();
        Common common = new Common();
        Boolean hasWhere = false;
        sql.append("select * from ( select p.*,date_format(p.purchase_date,'%Y-%m-%d') purchase_date1,tsw.id warehouse_id, " +
                " case model_name when p.car_model is null or p.car_model ='' then tbm.model_name else p.car_model end model_name," +
                " case tcc.configure_name when p.configure is null or p.configure ='' then tcc.configure_name else p.configure end configure_name ")
                .append(" from " + tabName + " p left join " + tabCarModel + " tbm on p.car_model_id=tbm.id")
                .append(" left join " + tabCarConfig + " tcc on p.configure_id=tcc.id")
                .append(" left join t_scm_stock tss on p.id = tss.car_id")
                .append(" left join t_scm_warehouse tsw on tsw.id = tss.warehouse_id ");
        //组织结构
        List<String> companyIdList = new ArrayList<>();
        if (data.get("companyId") != null && !data.get("companyId").equals("")) {
            companyIdList = (List<String>) data.get("companyId");
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" p.company_id in ( ");
            for (String id : companyIdList) {
                sql.append(" ?, ");
                params.add(id);
            }
            sql.append(" '' ) ");
        }
        //默认只能看自己组织下数据
        String cid = TokenUtil.getCompanyId(uid);
        if(!cid.isEmpty()){
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" p.company_id = ? ");
            params.add(cid);
        }
//        else {
//            companyIdList = this.organService.tree(root,"1");
//        }
        //红冲车辆查询
      /*  if (data.get("isRed") != null && !StringUtils.isEmpty(data.get("isRed"))) {
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" is_red = ? ");
            params.add(data.get("isRed"));
        }*/
        //车辆类型0采购在途1在库2已售3调拨在途(5销售发票添加车辆)
        if (!status.equals("")) {
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            if(status.equals("3")){
                sql.append(" p.status = ? ");
                sql.append(" AND EXISTS " +
                        "(select o.car_id from t_scm_outwarehouse o where o.outType = '2' and p.id = o.car_id) ");
                params.add(status);
            }else if(status.equals("1")){ //暂估入库的未入库
                sql.append(" p.status = ? ");
                sql.append(" AND EXISTS " +
                        "(select st.car_id from t_scm_stock st where st.car_id = p.id) ");
                params.add(status);
            } else if(status.equals("5")){//5销售发票添加车辆
                sql.append(" p.status = '1' and p.finance_type in (2,3)");
                sql.append(" AND EXISTS " +
                        "(select st.car_id from t_scm_stock st where st.car_id = p.id) ");
            }else{
                sql.append(" p.status = ? ");
                params.add(status);
            }
        }else{
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" p.status = '0' ");
        }
        //采购日期起
        if (data.get("purchaseStartDate") != null && !data.get("purchaseStartDate").equals("")) {
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" SUBSTR(p.purchase_date,1,10) >= ? ");
            params.add(data.get("purchaseStartDate"));
        }
        //采购日期到
        if (data.get("purchaseStopDate") != null && !data.get("purchaseStopDate").equals("")) {
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" SUBSTR(p.purchase_date,1,10) <= ? ");
            params.add(data.get("purchaseStopDate"));
        }
        //车系
        if (data.get("carSeriesCode") != null && !data.get("carSeriesCode").equals("")) {
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append("  p.car_series_id = ? ");
            params.add(data.get("carSeriesCode"));
            //车型代码
            if (data.get("carModelCode") != null && !data.get("carModelCode").equals("")) {
                hasWhere = common.appendWhereIfNeed(sql, hasWhere);
                sql.append("  p.car_model_id = ? ");
                params.add(data.get("carModelCode"));
                //车辆配置
                if (data.get("carConfigureCode") != null && !data.get("carConfigureCode").equals("")) {
                    hasWhere = common.appendWhereIfNeed(sql, hasWhere);
                    sql.append("  p.configure_id = ?");
                    params.add(data.get("carConfigureCode"));

                }
            }
        }
        //仓库
        if (data.get("warehouse") != null && !data.get("warehouse").equals("")) {
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" tss.warehouse_id = ? ");
            params.add(data.get("warehouse"));
        }
        //vin
        if (data.get("vin") != null && !data.get("vin").equals("")) {
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" p.vin like ? ");
            params.add("%" + data.get("vin") + "%");
        }
        //打印状态
       /* if (data.get("prientType") != null && !data.get("prientType").equals("")) {
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" p.validate_status = ? ");
            params.add(data.get("prientType"));
        }*/
        //提单号
        if (data.get("orderNum") != null && !data.get("orderNum").equals("")) {
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" p.old_num like ? ");
            params.add("%" + data.get("orderNum") + "%");
        }
        //销售状态
/*        if (data.get("sale") != null && data.get("sale").equals("1")) {
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" p.sale_type = 1 ");
            params.add("%" + data.get("sale") + "%");
        }*/
        //销售状态
        if (data.get("saleType") != null && data.get("saleType").equals("1")) {
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" p.sale_type = ? ");
            params.add( data.get("saleType"));
        }
       /* //出库状态(0未锁定1已锁定2待出库3确认出库)
        if (data.get("outWarehouseStatus") != null && !data.get("outWarehouseStatus").equals("")) {
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" p.out_warehouse_status = ? ");
            params.add(data.get("outWarehouseStatus"));
        }*/
        //财务状态
        if(data.get("financeType") != null && !data.get("financeType").equals("")){
            if(data.get("financeType").equals("1")){
                hasWhere = common.appendWhereIfNeed(sql, hasWhere);
                sql.append(" p.finance_type='1' ");
            }else{
                hasWhere = common.appendWhereIfNeed(sql, hasWhere);
                sql.append(" (p.finance_type is null or p.finance_type ='0')  ");
            }
        }
        sql.append(" order by p.create_time desc ) as temp  ");
        Map map = this.itScmCarDao.page(sql.toString(), params.toArray(), start, limit);
        return map;
    }


    /**
     * 刷新品牌
     *
     * @throws Exception
     */
    private void refreshCarBrand() throws Exception {
        //先查询每辆车的车系ID
        List<Map<String, Object>> list = this.itScmCarDao.list("select * from " + tabName + " where brand is null ");

        //根据车系ID去车系表里查询品牌
        for (Map map : list) {
            Map brand = this.itScmCarDao.info("select * from t_bc_car_series s where id = ?", map.get("carSeriesId"));
            if (!brand.isEmpty()) {
                //更新品牌
                StringBuffer sql = new StringBuffer();
                sql.append("update " + tabName + " set brand = ? where car_series_id = ?");
                this.itScmCarDao.executeSQL(sql.toString(), brand.get("brandCode"), map.get("carSeriesId"));
            }
        }
    }


    //刷新外观
    private void refreshCarAppearance(String uid) throws Exception {
        //刷新外观
        this.itScmCarDao.executeSQL("update t_scm_car c INNER JOIN t_scm_appearance a on c.car_series_id=a.car_series_id and c.appearance_code=a.appearance_code " +
                "set c.appearance_name = a.appearance_name " +
                "where c.company_id=? and (c.appearance_name is null or c.appearance_name='') and c.appearance_code is not null and c.car_series_id is not null ",
                TokenUtil.getCompanyId(uid));
    }

    //刷新内饰
    private void refreshCarInterior(String uid) throws Exception {
        //刷新内饰
        this.itScmCarDao.executeSQL("update t_scm_car c INNER JOIN t_scm_interior i on c.car_series_id=i.car_series_id and c.trim_code=i.interior_code " +
                        "set c.trim_name = i.interior_name " +
                        "where c.company_id=? and (c.trim_name is null or c.trim_name='') and c.trim_code is not null and c.car_series_id is not null ",
                TokenUtil.getCompanyId(uid));
    }

    /**
     * 刷新车辆内饰名称和外观名称
     *
     * @throws Exception
     */
    @Override
    public void refershCarAppearanceAndInterior(String uid) throws Exception {
        this.refreshCarAppearance(uid);
        this.refreshCarInterior(uid);
    }

    /**
     * 添加验车单打印次数
     *
     * @return
     * @throws Exception
     */
    @Override
    public boolean addValidateNum(String pid) throws Exception {

      /*  int a;
        StringBuffer sql = new StringBuffer();
        List params = new ArrayList();
        sql.append("select validate_num from " + tabName);
        if (!StringUtils.isEmpty(pid)) {
            sql.append(" where vin=?");
            params.add(pid);
        }

        Map map = this.itScmCarDao.info(sql.toString(), params.toArray());
        Object object = map.get("validateNum");

        if (map.size() > 0) {

            if (object != null) {
                a = (int) map.get("validateNum");
            } else {
                a = 0;
            }

            a++;*/
            StringBuffer sql2 = new StringBuffer();
            sql2.append(" UPDATE t_scm_car SET finance_type='1' WHERE vin=? and finance_type is null");
            List params2 = new ArrayList();
            //params2.add(a);
            params2.add(pid);
            this.itScmCarDao.executeSQL(sql2.toString(), params2.toArray());
       // }

        return true;

    }

    /**
     *
     * @param id
     * @param type  1:暂估入库  2 冲估入库 3 正式入库
     * @param price
     * @return
     * @throws Exception
     */
    @Override
    public boolean updateFinanceInfo(String id, String type, String price) throws Exception {
        if ( null == price){
            ///无价格数据，允许为0或空
            return false;
        }


        StringBuffer sqlCar = new StringBuffer();
        StringBuffer sqlCarPrice = new StringBuffer();
        List params2 = new ArrayList();
        if ("1".equals(type))
        {
            sqlCar.append(" UPDATE t_scm_car SET finance_type='2', estimate_price=? WHERE id=? and (finance_type is null or finance_type='0'or finance_type='1') ");

            params2.add(price);
            params2.add(id);
            this.itScmCarDao.executeSQL(sqlCar.toString(), params2.toArray());

            sqlCar.setLength(0);
            sqlCar.append(" UPDATE T_Scm_Car_Price_Detail SET estimate_price=?, estimate_date=? WHERE car_id=? and estimate_price is null ");  ////暂估为空时填入值，即只允许暂估一次
            params2.clear();
            params2.add( new BigDecimal("0"+ price));
            params2.add( Calendar.getInstance().getTime());
            params2.add(id);
            this.itScmCarDao.executeSQL(sqlCar.toString(), params2.toArray());
        }
        else if ("3".equals(type))
        {
            sqlCar.append(" UPDATE t_scm_car SET finance_type='3', invoice_price=? WHERE id=? and finance_type in ('2', '3') ");

            params2.add(price);
            params2.add(id);
            this.itScmCarDao.executeSQL(sqlCar.toString(), params2.toArray());

            sqlCar.setLength(0);
            ////无销售单时，允许修改
            sqlCar.append(" UPDATE T_Scm_Car_Price_Detail SET buy_price=?, in_date=? WHERE car_id=? and out_date is null ");
            params2.clear();
            params2.add( new BigDecimal("0"+ price));
            params2.add( Calendar.getInstance().getTime());
            params2.add(id);
            this.itScmCarDao.executeSQL(sqlCar.toString(), params2.toArray());
        }
        else if ("5".equals(type)) {
            sqlCar.append(" UPDATE T_Scm_Car_Price_Detail SET sale_price=?, out_date=?,out_type=(case when buy_price is null then 1 else 2 end) WHERE car_id=? and out_date is null ");
            params2.add( new BigDecimal("0"+ price));
            params2.add( Calendar.getInstance().getTime());
            params2.add(id);
            this.itScmCarDao.executeSQL(sqlCar.toString(), params2.toArray());
        }



        return true;
    }

    @Override
    public boolean financeConfirm(String[] ids){
        return false;
    }

    /**
     * 采购收货查询
     * @param data
     * @param start
     * @param limit
     * @param uid
     * @return
     * @throws Exception
     */
    @Override
    public Map CarMIGO(Map data, String start, String limit, String uid) throws Exception{

        StringBuffer sql = new StringBuffer();
        List params = new ArrayList();
        Common common = new Common();
        Boolean hasWhere = false;
        sql.append("select * from ( select p.*,SUBSTR(p.purchase_date, 1, 10) purchase_date1,tbm.model_name,tcc.configure_name,tsw.id warehouse_id ")
                .append(" from " + tabName + " p left join " + tabCarModel + " tbm on p.car_model_id=tbm.id")
                .append(" left join " + tabCarConfig + " tcc on p.configure_id=tcc.id")
                .append(" left join t_scm_stock tss on p.id = tss.car_id")
                .append(" left join t_scm_warehouse tsw on tsw.id = tss.warehouse_id ");
        //组织结构
        List<String> companyIdList = new ArrayList<>();
        if (data.get("companyId") != null && !data.get("companyId").equals("")) {
            companyIdList = (List<String>) data.get("companyId");
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" p.company_id in ( ");
            for (String id : companyIdList) {
                sql.append(" ?, ");
                params.add(id);
            }
            sql.append(" '' ) ");
        }
        //默认只能看自己组织下数据
        String cid = TokenUtil.getCompanyId(uid);
        if(!cid.isEmpty()){
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" p.company_id = ? ");
            params.add(cid);
        }
        //0采购入库 2 退车入库 3 调拨入库
        if(data.get("status") != null && !data.get("status").equals("")){
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            if(data.get("status").equals("2")){//退车入库
                sql.append(" p.status=? and  EXISTS ( select car_id from t_scm_outwarehouse ow")
                   .append(" where ow.need_back = '0' and ow.out_status ='1' and ow.outtype = '1')");
            }
            if(data.get("status").equals("3")){//调拨入库
                sql.append(" p.status=? and exists (select car_id from t_scm_outwarehouse ow")
                   .append(" where ow.need_back = '0' and out_status = '1' and outTYpe='2' and EXISTS")
                   .append(" (select vin from t_scm_allocation_order_detail o where o.vin = ow.vin))");
            }
            if(data.get("status").equals("0")){//采购入库
                sql.append("p.status=?");
            }
            params.add(data.get("status"));

        }
        //采购日期起
        if (data.get("purchaseStartDate") != null && !data.get("purchaseStartDate").equals("")) {
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" SUBSTR(p.purchase_date,1,10) >= ? ");
            params.add(data.get("purchaseStartDate"));
        }
        //采购日期到
        if (data.get("purchaseStopDate") != null && !data.get("purchaseStopDate").equals("")) {
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" SUBSTR(p.purchase_date,1,10) <= ? ");
            params.add(data.get("purchaseStopDate"));
        }
        //车系
        if (data.get("carSeriesCode") != null && !data.get("carSeriesCode").equals("")) {
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append("  p.car_series_id = ? ");
            params.add(data.get("carSeriesCode"));
            //车型代码
            if (data.get("carModelCode") != null && !data.get("carModelCode").equals("")) {
                hasWhere = common.appendWhereIfNeed(sql, hasWhere);
                sql.append("  p.car_model_id = ? ");
                params.add(data.get("carModelCode"));
                //车辆配置
                if (data.get("carConfigureCode") != null && !data.get("carConfigureCode").equals("")) {
                    hasWhere = common.appendWhereIfNeed(sql, hasWhere);
                    sql.append("  p.configure_id = ?");
                    params.add(data.get("carConfigureCode"));

                }
            }
        }
        //仓库
        if (data.get("warehouse") != null && !data.get("warehouse").equals("")) {
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" tss.warehouse_id = ? ");
            params.add(data.get("warehouse"));
        }
        //vin
        if (data.get("vin") != null && !data.get("vin").equals("")) {
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" p.vin like ? ");
            params.add("%" + data.get("vin") + "%");
        }

        //提单号
        if (data.get("orderNum") != null && !data.get("orderNum").equals("")) {
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" p.old_num like ? ");
            params.add("%" + data.get("orderNum") + "%");
        }

        //财务状态
        if(data.get("financeType") != null && !data.get("financeType").equals("")){
           if(data.get("status").equals("0")) {//采购入库
               if (data.get("financeType").equals("1")) {
                   hasWhere = common.appendWhereIfNeed(sql, hasWhere);
                   sql.append(" p.finance_type='1'");
               } else {
                   hasWhere = common.appendWhereIfNeed(sql, hasWhere);
                   sql.append(" (p.finance_type is null or p.finance_type ='0')  ");
               }
           }else{//调拨和销售
               hasWhere = common.appendWhereIfNeed(sql, hasWhere);
               sql.append(" p.finance_type not in ('0','1')");
           }

        }
        sql.append(" order by p.create_time desc ) as temp  ");
        Map map = this.itScmCarDao.page(sql.toString(), params.toArray(), start, limit);
        return map;
    }

    /**
     * 刷新车系车型配置（2017-10-25）
     * @param uid
     * @throws Exception
     */
    @Override
    public void refreshCarSMC(String uid) throws Exception{

        String cid = TokenUtil.getCompanyId(uid);
        String sql ="";
        //宝马(自营)
        if(this.itScmCarDao.list("select * from t_bc_organ where o_car_brand = 'BMW' and id = ?",cid).size()>0){
            sql = "select func_refreshBMW(?) ";
        }
        //奥迪(自营)
        if(this.itScmCarDao.list("select * from t_bc_organ where o_car_brand = 'Audi' and id = ?",cid).size()>0){
            sql = "select func_refreshAUDI(?) ";
        }
        //其他车辆(自营)
        if(this.itScmCarDao.list("select * from t_bc_organ where o_car_brand <> 'Audi' and o_car_brand <>'BMW' and id = ?",cid).size()>0){
            sql = "select func_refreshSMC(?) ";
        }
        List<String> params = new ArrayList<String>();
        if(!StringUtils.isEmpty(cid)){
            params.add(cid);
        }
        this.itScmCarDao.list(sql,params.toArray());
    }

    /**
     * 根据vin获取该车辆的信息（针对销售后重新采购回库的车）
     * @param vin
     * @param uid
     * @return
     * @throws Exception
     */
    @Override
    public List<TScmCar> queryByVin(String vin,String uid) throws Exception{

        String vv = "%"+vin+"%";
        String cid = TokenUtil.getCompanyId(uid);
        String hql = "from TScmCar where companyId = :companyId and vin like :vin";
        //设置参数
        Map<String, Object> params = new HashMap();
        params.put("companyId", cid);
        params.put("vin", vv);
        //查询公司下的全部车辆
        List<TScmCar> carList = iHibernateDao.findEntityListByHqlWithParams(hql, params);
        if(carList.size()==0){
            throw new Exception("找不到匹配车辆！");
        }
        return carList;
    }
}
